package com.ztesoft.report.utils.excel;


import com.ztesoft.report.base.pojo.R;
import com.ztesoft.report.entity.ReportUpload;
import com.ztesoft.report.entity.TempUploadLog;
import com.ztesoft.report.exception.RException;
import com.ztesoft.report.service.ReportUploadService;
import com.ztesoft.report.service.TempUploadLogService;
import com.ztesoft.report.utils.AESUtil;
import com.ztesoft.report.utils.ExcelDataExport;
import com.ztesoft.report.utils.excel.pojo.ExportItem;
import com.ztesoft.report.utils.ftp.SftpClient;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Component;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * 文件处理工具类
 *
 * @author y3tu
 * @date 2018/4/3
 */
@Component
@PropertySource(value = "classpath:backStageJob.properties")
public class FileUtils {

    @Autowired
    TempUploadLogService tempUploadLogService;

    @Autowired
    ReportUploadService reportUploadService;

    @Value("${com.ztesoft.downpath}")
    private String path;

    /**
     * 文件名加UUID
     *
     * @param filename
     * @return
     */
    public static String makeFileName(String filename) {
        return UUID.randomUUID().toString() + "_" + filename;
    }

    /**
     * 文件名特殊字符过滤
     *
     * @param str
     * @return
     * @throws PatternSyntaxException
     */
    public static String StringFilter(String str) throws PatternSyntaxException {
        String regEx = "[`~!@#$%^&*+=|{}':; ',//[//]<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]";
        Pattern p = Pattern.compile(regEx);
        Matcher m = p.matcher(str);
        return m.replaceAll("").trim();
    }


    /**
     * 生成Excel文件
     *
     * @param fileName   文件名称
     * @param clazz      导出数据对应的类
     * @param filePath   文件生成路径
     * @param headConfig 表头配置
     * @return
     */
    public  R createExcelByPOIKit(String fileName, String sheetName,
                                        int listCount, Class<?> clazz, String filePath,
                                        List<String> headConfig,Integer headTemp,Integer endTemp,String yyyyMMdd) throws Exception {

        if (listCount < 1) {
            throw new RException("导出数据为空!");
        } else {
            ExcelDataExport excelDataExport = null;
            try {
                FileOutputStream fos = null;
                fos = new FileOutputStream(filePath);
                //下载头部模板和尾部模板
                TempUploadLog tempUploadLogHead = tempUploadLogService.selectById(headTemp);
                TempUploadLog tempUploadLogEnd = tempUploadLogService.selectById(endTemp);
                //如果2个服务器相同就连接一次即可
                if (tempUploadLogHead.getServerId() != tempUploadLogEnd.getServerId()) {
                    ReportUpload reportUploadHead = reportUploadService.selectById(tempUploadLogHead.getServerId());
                    ReportUpload reportUploadEnd = reportUploadService.selectById(tempUploadLogEnd.getServerId());
                    String passwordHead = AESUtil.decrypt(reportUploadHead.getPassword());
                    String passwordEnd = AESUtil.decrypt(reportUploadEnd.getPassword());
                    //连接头部模板服务器
                    SftpClient sftpClientHead = SftpClient.connect(reportUploadHead.getHost(), Integer.parseInt(reportUploadHead.getPort()),
                            reportUploadHead.getUsername(), passwordHead, 2000, 2000);
                    //下载头部模板
                    sftpClientHead.get(tempUploadLogHead.getUploadPath() + "/" + tempUploadLogHead.getTempName(), path + File.separator + tempUploadLogHead.getTempName());
                    //连接尾部模板
                    SftpClient sftpClientEnd = SftpClient.connect(reportUploadEnd.getHost(), Integer.parseInt(reportUploadEnd.getPort()),
                            reportUploadEnd.getUsername(), passwordEnd, 2000, 2000);
                    //下载尾部模板
                    sftpClientEnd.get(tempUploadLogEnd.getUploadPath() + "/" + tempUploadLogEnd.getTempName(), path + File.separator + tempUploadLogEnd.getTempName());
                    sftpClientHead.disconnect();
                    sftpClientEnd.disconnect();
                } else {
                    ReportUpload reportUpload = reportUploadService.selectById(tempUploadLogHead.getServerId());
                    String password = AESUtil.decrypt(reportUpload.getPassword());
                    //连接头部模板服务器
                    SftpClient sftpClient = SftpClient.connect(reportUpload.getHost(), Integer.parseInt(reportUpload.getPort()),
                            reportUpload.getUsername(), password, 2000, 2000);
                    sftpClient.get(tempUploadLogHead.getUploadPath() + "/" + tempUploadLogHead.getTempName(), path + File.separator + tempUploadLogHead.getTempName());
                    sftpClient.get(tempUploadLogEnd.getUploadPath() + "/" + tempUploadLogEnd.getTempName(), path + File.separator + tempUploadLogEnd.getTempName());
                    sftpClient.disconnect();
                }
                //模板下载完后进行替换
                changeDateForExcel(path+File.separator+tempUploadLogHead.getTempName(),fileName.substring(0,fileName.lastIndexOf(".")),yyyyMMdd.substring(0,4),yyyyMMdd.substring(4,6),yyyyMMdd.substring(6,8));
                // 设置每个sheet的最大记录数,默认为10000,可不设置
                // .setMaxSheetRecords(10000)
                excelDataExport = ExcelUtils.Builder(clazz).toExcel(fileName, listCount, sheetName, fos, headConfig,path+File.separator+tempUploadLogHead.getTempName());
                //删除头部模板
                File headFile = new File(path+"/"+tempUploadLogHead.getTempName());
                if (headFile.exists()) {
                    headFile.delete();
                }
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }
            return R.ok(excelDataExport);
        }
    }

    /**
     * 替换一些字符串
     * @param oldData
     * @param newData
     * @param year
     * @param month
     * @param day
     * @return
     */
    public String changeData(String oldData,String newData,String year,String month,String day) {
        //理论上来说替换会一次全部替换完，如果出现不能替换的会出现死循环，所以count代表次数，如果一次没替换完，说明存在错误替换字符
        int count = 0;
        String nowTime = new SimpleDateFormat("yyyyMMdd").format(new Date());
        while (oldData.contains("#{{")) {
            if (count > 1) {
                throw new RException("存在错误的替换字符，不在#{{YYYYMMDD}}、#{{YYYY}}、#{{MM}}、#{{DD}}行列");
            }
            oldData = oldData.replace("#{{TITLE}}",newData);
            oldData = oldData.replace("#{{YYYY}}",year);
            oldData = oldData.replace("#{{MM}}",month);
            oldData = oldData.replace("#{{DD}}",day);
            oldData = oldData.replace("#{{YYYYMMDD}}",nowTime);
            count++;
        }
        return oldData;
    }

    /**
     * 这里会把生成好的EXCEL，对其中年月日进行替换
     * @param path 生成的Excel路径
     * @param excelTitle 标题
     * @param year 年
     * @param month 月
     * @param day 日
     */
    public void changeDateForExcel(String path,String excelTitle,String year,String month,String day) {
        FileInputStream inStream = null;
        FileOutputStream os = null;
        try {
            inStream  = new FileInputStream(new File(path));
            Workbook wb = WorkbookFactory.create(inStream);
            //sheet页总页数
            int sheetCount = wb.getNumberOfSheets();
            //遍历每个单元格，去替换年月日
            //sheet页层
            for (int i = 0;i < sheetCount; i++) {
                //进入第i页
                Sheet sheet = wb.getSheetAt(i);
                //Row行层
                for (int j = 0; j <= sheet.getLastRowNum(); j++) {
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        //Cell单元格层
                        for (int k = 0; k < row.getLastCellNum(); k++) {
                            Cell cell = row.getCell(k);
                            if (cell != null) {
                                cell.setCellType(CellType.STRING);
                                String info = cell.getStringCellValue();
                                if (info.contains("#{{")) {
                                    String newData = changeData(info, excelTitle, year, month, day);
                                    cell.setCellValue(newData);
                                }
                            }
                        }
                    }
                }
            }
            os = new FileOutputStream(path);
            wb.write(os);
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RException("Excel日期替换中出错："+e.getMessage());
        } finally {
            try {
                if (inStream != null) {
                    inStream.close();
                }
                if (os != null) {
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 生成Csv文件
     *
     * @param fileName
     * @param clazz
     * @return
     */
    public static R createCsv(String fileName, List<?> data, Class<?> clazz,
                              String filePath, boolean displayHeader, Object object) {
        List<ExportItem> exportItems = new ArrayList<>();
        return createCsv(fileName, data, clazz, filePath, exportItems, displayHeader);
    }

    /**
     * 生成Csv文件
     *
     * @param fileName
     * @param list
     * @param clazz
     * @return
     */
    public static R createCsv(String fileName, List<?> list, Class<?> clazz, String filePath, List<ExportItem> exportItemList, boolean displayHeader) {

        if (list.isEmpty()) {
            throw new RException("导出数据为空!");
        } else {
            boolean operateSign = false;
            try {
                operateSign = ExcelUtils.Builder(clazz).toCsv(list, filePath, exportItemList, displayHeader);
            } catch (Exception e) {
                e.printStackTrace();
            }
            if (operateSign) {
                return R.ok(fileName);
            } else {
                throw new RException("导出Csv失败，请联系网站管理员！");
            }
        }
    }

    public static void zipFiles(File[] srcfile, File zipfile) {
        zipFiles(srcfile, null, zipfile);
    }

    public static void zipFiles(File[] srcfile, String[] fileNames, File zipfile) {
        byte[] buf = new byte[1024];
        ZipOutputStream out = null;
        try {
            out = new ZipOutputStream(new FileOutputStream(zipfile));
            for (int i = 0; i < srcfile.length; i++) {
                if (srcfile[i] == null) {
                    continue;
                }
                FileInputStream in = null;
                try {
                    in = new FileInputStream(srcfile[i]);
                    String fileName = srcfile[i].getName();
                    if (fileNames != null) {
                        if (StringUtils.isNotEmpty(fileNames[i])) {
                            fileName = fileNames[i];
                        }
                    }
                    out.putNextEntry(new ZipEntry(fileName));
                    int len;
                    while ((len = in.read(buf)) > 0) {
                        out.write(buf, 0, len);
                    }

                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                } finally {
                    if (in != null) {
                        try {
                            in.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                    if (out != null) {
                        try {
                            out.closeEntry();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }
    }
}
